﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Web.Configuration;

namespace LeadVinaglue.Utility
{
    public static class Data
    {
        public static string connString = WebConfigurationManager.ConnectionStrings["dbConn"].ToString();

        static Data()
        {
            connString = GetConnString();
        }

        private static string GetConnString()
        {
            try
            {
                return WebConfigurationManager.ConnectionStrings["dbConn"].ToString(); 
                //return System.Configuration.ConfigurationManager.AppSettings["TopDogEntities"].ToString(); 
            }
            catch (Exception)
            {
                throw new Exception("Encrypt the db connection strings. <br>0. Add a .net reference to System.Configuration 1. Add a <connectionStrings> to your web.config. <br>2. Ensure that you have uncommented the 'configProtectedData' section in machine.config. <br>3. Use (Import to your web solution, then delete once you are done..) the OAS.Admin.Schools.Creighton/EncryptConfiguration.aspx to properly configure the web.config file.<br>Contact Kalin for answers.");
            }
        }
       
        public static int ExecuteScalar(string sql)
        {
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            try
            {
                conn = new System.Data.SqlClient.SqlConnection(connString);
                cmd = new System.Data.SqlClient.SqlCommand(sql, conn);
                conn.Open();
                return (int)cmd.ExecuteScalar();
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                cmd = null;
                conn.Close();
            }
        }
        public static T ExecuteScalar<T>(string sql)
        {
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            try
            {
                conn = new System.Data.SqlClient.SqlConnection(connString);
                cmd = new System.Data.SqlClient.SqlCommand(sql, conn);
                conn.Open();
                return (T) cmd.ExecuteScalar();                
            }
            catch (Exception x)
            {
                throw x;
            }              
            finally
            {
                cmd = null;
                conn.Close();
            }
        }

        public static IDataReader ExecuteReader(string sql)
        {
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            try
            {
                conn = new System.Data.SqlClient.SqlConnection(connString);
                cmd = new System.Data.SqlClient.SqlCommand(sql, conn);
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                cmd = null;
                
             //   conn.Close();
            }
        }

        public static int ExecuteNonQuery(string sql)
        {
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            try
            {
                conn = new System.Data.SqlClient.SqlConnection(connString);
                cmd = new System.Data.SqlClient.SqlCommand(sql, conn);
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                cmd = null;
                conn.Close();
            }
        }
        public static int ExecuteNonQuery(System.Data.CommandType commandType,string sql)
        {
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            try
            {
                conn = new System.Data.SqlClient.SqlConnection(connString);
                cmd = new System.Data.SqlClient.SqlCommand(sql, conn);
                cmd.CommandType = commandType;
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                cmd = null;
                conn.Close();
            }
        }

        public static System.Data.DataSet ExecuteDataSet(System.Data.CommandType commandType,string sql)
        {
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            try
            {
                conn = new System.Data.SqlClient.SqlConnection(connString);
                cmd = new System.Data.SqlClient.SqlCommand(sql, conn);
                cmd.CommandType = commandType;
                System.Data.DataSet ds = new System.Data.DataSet();
                conn.Open();
                System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
                da.Fill(ds);
                return ds;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                cmd = null;
                conn.Close();
            }
        }

        public static System.Data.DataSet ExecuteDataSetByStoredProcedure(string sProcName, List<LeadVinaglue.Utility.DBParameter> dbParams)
        {
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            try
            {
                conn = new System.Data.SqlClient.SqlConnection(connString);
                cmd = new System.Data.SqlClient.SqlCommand(sProcName, conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                AddParametersToSqlCommand(dbParams, cmd);
                System.Data.DataSet ds = new System.Data.DataSet();
                conn.Open();
                System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
                da.Fill(ds);

                foreach (LeadVinaglue.Utility.DBParameter item in dbParams)
                    item.Value = cmd.Parameters[item.ParamName].Value;
                return ds;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                cmd = null;
                conn.Close();
            }
        }

        public static void AddParametersToSqlCommand(List<LeadVinaglue.Utility.DBParameter> dbParams, System.Data.SqlClient.SqlCommand cmd)
        {
            foreach (LeadVinaglue.Utility.DBParameter dbParam in dbParams)
            {
                if (dbParam.DbParameterAddType == LeadVinaglue.Utility.SQLParameterAddType.Add)
                {
                    cmd.Parameters.Add(dbParam.ParamName, dbParam.DbTypeForParam,dbParam.Size);
                    cmd.Parameters[dbParam.ParamName].Direction = dbParam.DbParameterDirection;
                }
                else
                {
                    cmd.Parameters.AddWithValue(dbParam.ParamName, dbParam.Value);
                }
            }
        }

        public static int ExecuteNonQueryByStoredProcedure(string sProcName, List<LeadVinaglue.Utility.DBParameter> dbParams)
        {
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            try
            {
                conn = new System.Data.SqlClient.SqlConnection(connString);
                cmd = new System.Data.SqlClient.SqlCommand(sProcName, conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                AddParametersToSqlCommand(dbParams, cmd);
                conn.Open();
                int retVal = cmd.ExecuteNonQuery();
                foreach (LeadVinaglue.Utility.DBParameter item in dbParams)
                    item.Value = cmd.Parameters[item.ParamName].Value;
                return retVal;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                cmd = null;
                conn.Close();
            }
        }
    }

    public class DBParameter
    {

        public DBParameter()
        {

        }

        public DBParameter(System.Data.SqlDbType dbTypeParam, string parameterNameParam, object valueParam, System.Data.ParameterDirection dbParameterDirection, LeadVinaglue.Utility.SQLParameterAddType addType)
            :this()
        {
            this.dbType = dbTypeParam;
            this.parameterName = parameterNameParam;
            this.valueParam = valueParam;
            this.dbParameterAddType = addType;
            this.dbParameterDirection = dbParameterDirection;
        }

        private System.Data.SqlDbType dbType;

        public System.Data.SqlDbType DbTypeForParam
        {
            get { return dbType; }
            set { dbType = value; }
        }
        private string parameterName;

        public string ParamName
        {
            get { return parameterName; }
            set { parameterName = value; }
        }
        private object valueParam;

        public object Value
        {
            get { return valueParam; }
            set { valueParam = value; }
        }

        public System.Data.ParameterDirection dbParameterDirection;
        public System.Data.ParameterDirection DbParameterDirection
        {
            get { return dbParameterDirection; }
            set { dbParameterDirection = value; }
        }

        public LeadVinaglue.Utility.SQLParameterAddType dbParameterAddType;
        public LeadVinaglue.Utility.SQLParameterAddType DbParameterAddType
        {
            get { return dbParameterAddType; }
            set { dbParameterAddType = value; }
        }

        public int Size { get; set; }


    }

    public enum SQLParameterAddType
    {
        Add,
        AddWithValue
    }

}
